<?php


namespace Account\Model;

use Zend\Db\Sql\Select;
use Zend\Db\Sql\Sql;
use Zend\Db\TableGateway\TableGateway;

class AccountTable
{
    protected $tableGateway;
    protected $adapter;

    public function __construct(TableGateway $tableGateway)
    {
        $this->tableGateway = $tableGateway;
        $this->adapter = $tableGateway->getAdapter();
    }

    /**
     * 获取所有账户
     * @return array
     */
    public function fetchAll()
    {
        $adapter = $this->adapter;
        $sql = new Sql($adapter);
        $select = new Select();
        $select->from('pc_account')
            ->join('pc_bank', 'pc_account.bank_id = pc_bank.bank_id');
        $statement = $sql->prepareStatementForSqlObject($select);
        $resultSet = $statement->execute();
        return iterator_to_array($resultSet);
    }

    /**
     * 获取所有支票账户
     * @return array
     */
    public function getCheck()
    {
        $adapter = $this->adapter;
        $sql = new Sql($adapter);
        $select = new Select();
        $select->from('pc_check_account')
            ->join('pc_bank', 'pc_check_account.bank_id = pc_bank.bank_id');
        $statement = $sql->prepareStatementForSqlObject($select);
        $resultSet = $statement->execute();
        return iterator_to_array($resultSet);
    }

    /**
     * 获取所有储蓄账户
     * @return array
     */
    public function getThrift()
    {
        $adapter = $this->adapter;
        $sql = new Sql($adapter);
        $select = new Select();
        $select->from('pc_thrift_account')
            ->join('pc_bank', 'pc_thrift_account.bank_id = pc_bank.bank_id');
        $statement = $sql->prepareStatementForSqlObject($select);
        $resultSet = $statement->execute();
        return iterator_to_array($resultSet);
    }

    /**
     * 获取某客户的所有账户
     * @param $client_id
     * @return array
     */
    public function getAccountByClientId($client_id)
    {
        $sql = "SELECT account_id, bank_name, account_balance, rate, overdraft FROM pc_account JOIN pc_bank ON pc_account.bank_id = pc_bank.bank_id WHERE account_id IN (SELECT account_id FROM pc_have WHERE client_id = '" . $client_id . "');";
        $resultSet = $this->tableGateway->getAdapter()->query($sql, array());
        return $resultSet->toArray();
    }

    /**
     * 获取某客户的所有支票账户
     * @param $client_id
     * @return array
     */
    public function getCheckByClientId($client_id)
    {
        $sql = "SELECT account_id, bank_name, account_balance, rate, overdraft FROM pc_account JOIN pc_bank ON pc_account.bank_id = pc_bank.bank_id WHERE account_id IN (SELECT account_id FROM pc_have WHERE client_id = '" . $client_id . "' AND account_id IN (SELECT account_id FROM pc_check_account));";
        $resultSet = $this->tableGateway->getAdapter()->query($sql, array());
        return $resultSet->toArray();
    }

    /**
     * 获取某客户的所有储蓄账户
     * @param $client_id
     * @return array
     */
    public function getThriftByClientId($client_id)
    {
        $sql = "SELECT account_id, bank_name, account_balance, rate FROM pc_account JOIN pc_bank ON pc_account.bank_id = pc_bank.bank_id WHERE account_id IN (SELECT account_id FROM pc_have WHERE client_id = '" . $client_id . "' AND account_id IN (SELECT account_id FROM pc_thrift_account));";
        $resultSet = $this->tableGateway->getAdapter()->query($sql, array());
        return $resultSet->toArray();
    }

    /**
     * 获取某支行的所有账户
     * @param $bank_id
     * @return array
     */
    public function getAccountByBankId($bank_id)
    {
        $resultSet = $this->tableGateway->select(array('bank_id' => $bank_id));
        return $resultSet->toArray();
    }

    /**
     * 获取某支行的所有支票账户
     * @param $bank_id
     * @return array
     */
    public function getCheckByBankId($bank_id)
    {
        $sql = "SELECT * FROM pc_account WHERE account_id IN (SELECT account_id FROM pc_check_account) AND bank_id = " . $bank_id . ";";
        $resultSet = $this->tableGateway->getAdapter()->query($sql, array());
        return $resultSet->toArray();
    }

    /**
     * 获取某支行的所有储蓄账户
     * @param $bank_id
     * @return array
     */
    public function getThriftByBankId($bank_id)
    {
        $sql = "SELECT * FROM pc_account WHERE account_id IN (SELECT account_id FROM pc_thrift_account) AND bank_id = " . $bank_id . ";";
        $resultSet = $this->tableGateway->getAdapter()->query($sql, array());
        return $resultSet->toArray();
    }
}